using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using gudusoft.gsqlparser;
using gudusoft.gsqlparser.Units;
using System.IO;
using System.Xml.Linq;

namespace ColumnImpact
{
    enum ClauseType
    {
        where, connectby, startwith, orderby, join, undefine, select, groupby
    }

    class TResultEntry
    {
        public TResultEntry(TLzTable table, String column, ClauseType clause)
        {
            this.targetTable = table;
            this.targetColumn = column;
            this.clause = clause;
        }

        public string targetColumn;
        public TLzTable targetTable;
        public ClauseType clause;
    }

    class TAlias
    {
        public string column;
        public string alias;
    }

    class TSourceColumn
    {
        public string tableOwner;
        public string tableName;
        public string name;
        public List<ClauseType> clauses = new List<ClauseType>();
    }

    public interface IMetaDatabaseFilter
    {
        bool metaDatabaseTableColumn(string tableOwner, string tableName, string columnName);
    }

    public class TColumn
    {
        public List<String> tableNames = new List<String>();
        public string columnName;
        public string columnPrex;
        public string orignColumn;

        public string getFullName(String tableName)
        {
            if (tableName != null)
            {
                return tableName + "." + columnName;
            }
            else
            {
                return columnName;
            }
        }

        public string getOrigName()
        {
            if (columnPrex != null)
            {
                return columnPrex + "." + columnName;
            }
            else
            {
                return columnName;
            }
        }

    }

    class TTable
    {
        public string tableName;
        public string prefixName;
        public string tableAlias;
    }

    public class Utf8StringWriter : StringWriter
    {
        public Utf8StringWriter(StringBuilder sb) : base(sb) { }

        public override Encoding Encoding { get { return Encoding.UTF8; } }
    }

    class columnsInExpr
    {

        private TLzCustomExpression expr;
        private List<TColumn> columns;
        private int level;
        private columnImpact impact;
        private TCustomSqlStatement stmt;
        public columnsInExpr(columnImpact impact, TLzCustomExpression expr, List<TColumn> columns, TCustomSqlStatement stmt, int level)
        {
            this.stmt = stmt;
            this.impact = impact;
            this.expr = expr;
            this.columns = columns;
            this.level = level;
        }

        private Boolean searchColumnVisitor(TLz_Node pnode, Boolean pIsLeafNode)
        {
            TLzCustomExpression lcexpr = (TLzCustomExpression)pnode;
            if (lcexpr.oper == TLzOpType.Expr_Attr)
            {
                columns.Add(impact.attrToColumn((TLz_Attr)lcexpr.lexpr, stmt));
            }
            else if (lcexpr.oper == TLzOpType.Expr_FuncCall)
            {
                TLz_FuncCall func = (TLz_FuncCall)lcexpr.lexpr;
                if (func.args != null)
                {
                    for (int k = 0; k < func.args.Count(); k++)
                    {
                        if (func.args[k] is TLzCustomExpression)
                        {
                            TLzCustomExpression expr = (TLzCustomExpression)func.args[k];
                            expr.InOrderTraverse(searchColumnVisitor);
                        }
                    }
                }
                if (func._analyticFunction != null)
                {
                    TLz_List list = func._analyticFunction._partitionExprList;
                    addColumnToList(list, stmt);

                    if (func._analyticFunction._sortclause != null)
                    {
                        list = func._analyticFunction._sortclause.list1;
                        addColumnToList(list, stmt);

                        list = func._analyticFunction._sortclause.list2;
                        addColumnToList(list, stmt);
                    }
                }

            }
            else if (lcexpr.oper == TLzOpType.Expr_subquery)
            {
                if (lcexpr.lexpr is TSelectSqlStatement)
                {
                    foreach (TLzField field in ((TSelectSqlStatement)lcexpr.lexpr).Fields)
                    {
                        impact.findColumnInSubQuery((TSelectSqlStatement)lcexpr.lexpr, field.AsText, level);
                    }
                }
            }
            else if (lcexpr.oper == TLzOpType.Expr_Case)
            {
                TLzCaseExpression expr = (TLzCaseExpression)lcexpr;
                TLzCustomExpression conditionExpr = expr.ConditionExpr;
                if (conditionExpr != null)
                {
                    conditionExpr.InOrderTraverse(searchColumnVisitor);
                }
                TLzCustomExpression defaultExpr = expr.DefaultExpr;
                if (defaultExpr != null)
                {
                    defaultExpr.InOrderTraverse(searchColumnVisitor);
                }
                TLz_List list = expr.WhenThenList;
                addColumnToList(list, stmt);
            }
            return true;
        }

        private void addColumnToList(TLz_List list, TCustomSqlStatement stmt)
        {
            if (list != null)
            {
                for (int i = 0; i < list.Count(); i++)
                {
                    List<TLzCustomExpression> exprList = new List<TLzCustomExpression>();

                    if (list[i] is TLz_SortGroupBy)
                    {
                        exprList.Add((TLzCustomExpression)((TLz_SortGroupBy)list[i]).node);
                    }
                    else if (list[i] is TLzCustomExpression)
                    {
                        exprList.Add((TLzCustomExpression)list[i]);
                    }
                    else if (list[i] is TLzCaseWhen)
                    {
                        exprList.Add(((TLzCaseWhen)list[i]).WhenExpr);
                        exprList.Add(((TLzCaseWhen)list[i]).ThenExpr);
                    }

                    foreach (TLzCustomExpression lcexpr in exprList)
                    {
                        lcexpr.InOrderTraverse(searchColumnVisitor);
                    }
                }
            }
        }

        public void searchColumn()
        {
            this.expr.InOrderTraverse(searchColumnVisitor);
        }
    }


    public class columnImpact
    {
        /*store the dependency relations*/
        Hashtable dependMap = new Hashtable();
        /*store the relations of alias to column*/
        List<TAlias> aliases = new List<TAlias>();
        String currentSource = null;
        Boolean simply = false;
        Boolean isXML = false;
        Hashtable cteMap = new Hashtable();
        StringBuilder buffer = new StringBuilder();
        Hashtable currentClauseMap = new Hashtable();
        IMetaDatabaseFilter filter;

        public String ImpactResult
        {
            get { return buffer.ToString(); }
        }


        public columnImpact(String sql, TDbVendor dbVendor, Boolean simply, Boolean isXML)
        {
            this.simply = simply;
            this.isXML = isXML;
            TGSqlParser sqlparser = new TGSqlParser(dbVendor);
            sqlparser.SqlText.Text = sql;
            impactSQL(sqlparser);
        }

        public columnImpact(FileInfo file, TDbVendor dbVendor, Boolean simply, Boolean isXML)
        {
            this.simply = simply;
            this.isXML = isXML;
            TGSqlParser sqlparser = new TGSqlParser(dbVendor);
            sqlparser.Sqlfilename = file.ToString();
            impactSQL(sqlparser);
        }

        public columnImpact(String sql, TDbVendor dbVendor, Boolean simply, Boolean isXML, IMetaDatabaseFilter filter)
        {
            this.simply = simply;
            this.isXML = isXML;
            this.filter = filter;
            TGSqlParser sqlparser = new TGSqlParser(dbVendor);
            sqlparser.SqlText.Text = sql;
            impactSQL(sqlparser);
        }

        public columnImpact(FileInfo file, TDbVendor dbVendor, Boolean simply, Boolean isXML, IMetaDatabaseFilter filter)
        {
            this.simply = simply;
            this.isXML = isXML;
            this.filter = filter;
            TGSqlParser sqlparser = new TGSqlParser(dbVendor);
            sqlparser.Sqlfilename = file.ToString();
            impactSQL(sqlparser);
        }


        private void impactSQL(TGSqlParser sqlparser)
        {
            int ret = sqlparser.Parse();

            if (ret != 0)
            {
                buffer.AppendLine(sqlparser.ErrorMessages);
            }
            else
            {
                XDocument doc = null;
                XElement columnImpactResult = null;
                if (simply && isXML)
                {
                    doc = new XDocument();
                    columnImpactResult = new XElement("columnImpactResult");
                }

                for (int k = 0; k < sqlparser.SqlStatements.Count(); k++)
                {
                    if (sqlparser.SqlStatements[k] is TCustomSqlStatement)
                    {
                        dependMap.Clear();
                        aliases.Clear();
                        currentSource = null;
                        cteMap.Clear();
                        currentClauseMap.Clear();

                        TCustomSqlStatement select = (TCustomSqlStatement)sqlparser.SqlStatements[k];
                        if (select.cte != null && select.cte.ctes != null && select.cte.ctes.Count() > 0)
                        {
                            for (int i = 0; i < select.cte.ctes.Count(); i++)
                            {
                                TCommonTableExpression expression = (TCommonTableExpression)select.cte.ctes[i];
                                cteMap.Add(expression.ndTablename.AsText, expression.ndSelect_with_parens);
                            }
                        }

                        impactSqlFromStatement(select);

                        if (simply)
                        {
                            if (!isXML)
                            {
                                foreach (TAlias alias in aliases)
                                {

                                    buffer.Append(alias.alias + " depends on: ");

                                    HashSet<String> collections = new HashSet<String>();

                                    if (dependMap.ContainsKey(alias.alias))
                                    {
                                        List<TResultEntry> results = (List<TResultEntry>)dependMap[alias.alias];
                                        foreach (TResultEntry result in results)
                                        {
                                            if ("*".Equals(result.targetColumn))
                                            {
                                                collections.Add(result.targetTable.TableFullname.ToLower());
                                            }
                                            else
                                            {
                                                collections.Add((result.targetTable.TableFullname + "." + result.targetColumn).ToLower());
                                            }
                                        }
                                    }

                                    List<String> list = new List<String>(collections);
                                    for (int i = 0; i < list.Count; i++)
                                    {
                                        if (i < collections.Count - 1)
                                            buffer.Append(list[i] + ", ");
                                        else
                                            buffer.Append(list[i]);
                                    }

                                    buffer.AppendLine();

                                }
                            }
                            else
                            {

                                foreach (TAlias alias in aliases)
                                {
                                    XElement targetColumn = new XElement("targetColumn");
                                    targetColumn.Add(new XAttribute("name", alias.column));
                                    if (!alias.alias.Equals(alias.column))
                                        targetColumn.Add(new XAttribute("alias", alias.alias));
                                    columnImpactResult.Add(targetColumn);

                                    Hashtable collections = new Hashtable();

                                    if (dependMap.ContainsKey(alias.alias))
                                    {
                                        List<TResultEntry> results = (List<TResultEntry>)dependMap[alias.alias];
                                        foreach (TResultEntry result in results)
                                        {
                                            String key = null;
                                            if ("*".Equals(result.targetColumn))
                                            {
                                                key = result.targetTable.TableFullname.ToLower();
                                            }
                                            else
                                            {
                                                key = (result.targetTable.TableFullname + "." + result.targetColumn).ToLower();
                                            }

                                            TSourceColumn sourceColumn = null;
                                            if (collections.ContainsKey(key))
                                            {
                                                sourceColumn = (TSourceColumn)collections[key];
                                                if (!sourceColumn.clauses.Contains(result.clause) && !ClauseType.undefine.Equals(result.clause))
                                                {
                                                    sourceColumn.clauses.Add(result.clause);
                                                }
                                            }
                                            else
                                            {
                                                sourceColumn = new TSourceColumn();
                                                collections.Add(key, sourceColumn);
                                                sourceColumn.tableOwner = result.targetTable.TableOwner;
                                                sourceColumn.tableName = result.targetTable.TableName;
                                                if (!"*".Equals(result.targetColumn))
                                                {
                                                    sourceColumn.name = result.targetColumn;
                                                }
                                                if (!sourceColumn.clauses.Contains(result.clause) && !ClauseType.undefine.Equals(result.clause))
                                                {
                                                    sourceColumn.clauses.Add(result.clause);
                                                }
                                            }
                                        }

                                        IEnumerator iter = collections.Values.GetEnumerator();
                                        while (iter.MoveNext())
                                        {
                                            TSourceColumn sourceColumn = (TSourceColumn)iter.Current;
                                            if (sourceColumn.clauses.Count > 0)
                                            {
                                                for (int j = 0; j < sourceColumn.clauses.Count; j++)
                                                {
                                                    ClauseType clause = sourceColumn.clauses[j];
                                                    XElement element = new XElement("sourceColumn");
                                                    if (sourceColumn.tableOwner != null)
                                                    {
                                                        XAttribute attribute = new XAttribute("tableOwner", sourceColumn.tableOwner);
                                                        element.Add(attribute);
                                                    }
                                                    if (sourceColumn.tableName != null)
                                                    {
                                                        XAttribute attribute = new XAttribute("tableName", sourceColumn.tableName);
                                                        element.Add(attribute);
                                                    }
                                                    if (sourceColumn.name != null)
                                                    {
                                                        XAttribute attribute = new XAttribute("name", sourceColumn.name);
                                                        element.Add(attribute);
                                                    }
                                                    {
                                                        StringBuilder buffer = new StringBuilder();
                                                        switch (clause)
                                                        {
                                                            case ClauseType.where:
                                                                buffer.Append("where");
                                                                break;
                                                            case ClauseType.connectby:
                                                                buffer.Append("connect by");
                                                                break;
                                                            case ClauseType.startwith:
                                                                buffer.Append("start with");
                                                                break;
                                                            case ClauseType.orderby:
                                                                buffer.Append("order by");
                                                                break;
                                                            case ClauseType.join:
                                                                buffer.Append("join");
                                                                break;
                                                            case ClauseType.select:
                                                                buffer.Append("select");
                                                                break;
                                                            case ClauseType.groupby:
                                                                buffer.Append("group by");
                                                                break;
                                                        }
                                                        XAttribute attribute = new XAttribute("clause", buffer.ToString());
                                                        element.Add(attribute);
                                                    }
                                                    targetColumn.Add(element);
                                                }
                                            }
                                            else
                                            {
                                                XElement element = new XElement("sourceColumn");
                                                if (sourceColumn.tableOwner != null)
                                                {
                                                    XAttribute attribute = new XAttribute("tableOwner", sourceColumn.tableOwner);
                                                    element.Add(attribute);
                                                }
                                                if (sourceColumn.tableName != null)
                                                {
                                                    XAttribute attribute = new XAttribute("tableName", sourceColumn.tableName);
                                                    element.Add(attribute);
                                                }
                                                if (sourceColumn.name != null)
                                                {
                                                    XAttribute attribute = new XAttribute("name", sourceColumn.name);
                                                    element.Add(attribute);
                                                }
                                                targetColumn.Add(element);
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }

                if (doc != null)
                {
                    doc.Add(columnImpactResult);
                    using (StringWriter writer = new Utf8StringWriter(buffer))
                    {
                        doc.Save(writer, SaveOptions.None);
                    }
                }
            }
        }

        private void impactSqlFromStatement(TCustomSqlStatement select)
        {
            if (select is TSelectSqlStatement)
            {
                TSelectSqlStatement stmt = (TSelectSqlStatement)select;
                if (stmt.SelectSetType != TSelectSetType.sltNone)
                {
                    impactSqlFromStatement(stmt.LeftStmt);
                    impactSqlFromStatement(stmt.RightStmt);
                }
                else
                {
                    foreach (TLzField field in select.Fields)
                    {
                        linkFieldToTables(field, select, 0);
                    }
                }
            }
            else if (select is TInsertSqlStatement && ((TInsertSqlStatement)select).subquery != null)
            {
                impactSqlFromStatement(((TInsertSqlStatement)select).subquery);
            }
            else
            {
                foreach (TLzField field in select.Fields)
                {
                    linkFieldToTables(field, select, 0);
                }
            }
        }

        class MetaDatabaseFilter : IMetaDatabaseFilter
        {
            public bool metaDatabaseTableColumn(string tableOwner, string tableName, string columnName)
            {
                if ("other_table".Equals(tableName))
                {
                    if ("c1".Equals(columnName))
                        return true;
                    else return false;
                }
                if ("some_table".Equals(tableName))
                {
                    if ("c1".Equals(columnName))
                        return false;
                    else return true;
                }
                return true;
            }
        }

        static void Main(string[] args)
        {
            if (args.Length == 0)
            {
                Console.WriteLine("Usage: {0} scriptfile [/s]/[/d] [/xml] [/o <output file path>] [/t <database type>]", Environment.GetCommandLineArgs()[0]);
                Console.WriteLine("/s: Option, display the analysis result simply.");
                Console.WriteLine("/d: Option, display the analysis result in detail.");
                Console.WriteLine("/xml: Option, export the analysis results to XML format, it's valid only if /s is specified");
                Console.WriteLine("/o: Option, write the output stream to the specified file.");
                Console.WriteLine("/t: Option, set the database type. Support oracle,mssql,mysql and db2, the default type is oracle");
                //Console.Read();
                return;
            }


            Array array = Array.CreateInstance(typeof(string), args.Length);

            for (int i = 0; i < array.Length; i++)
            {
                array.SetValue(args[i], i);
            }

            bool simply = Array.IndexOf(array, "/s") != -1;

            bool isXML = simply && Array.IndexOf(array, "/xml") != -1;

            string outputFile = null;

            int index = Array.IndexOf(array, "/o");

            if (index != -1 && args.Length > index + 1)
            {
                outputFile = args[index + 1];
            }

            StreamWriter writer = null;
            if (outputFile != null)
            {
                try
                {
                    writer = new StreamWriter(outputFile);
                    Console.SetOut(writer);
                }
                finally { }
            }

            TDbVendor vendor = TDbVendor.DbVOracle;

            index = Array.IndexOf(array, "/t");

            if (index != -1 && args.Length > index + 1)
            {
                if (args[index + 1].Equals("mssql"))
                {
                    vendor = TDbVendor.DbVMssql;
                }
                else if (args[index + 1].Equals("db2"))
                {
                    vendor = TDbVendor.DbVDB2;
                }
                else if (args[index + 1].Equals("mysql"))
                {
                    vendor = TDbVendor.DbVMysql;
                }
            }


            columnImpact impact = new columnImpact(new FileInfo(args[0]), vendor, simply, isXML, new MetaDatabaseFilter());
            Console.Write(impact.ImpactResult);

            if (!simply)
            {
                Console.WriteLine(System.Environment.NewLine + "You can add /s directive to display the analysis result in a simple format.");
            }

            //if (outputFile == null)
            //{
            //    Console.Read();
            //}
            //else
            {
                if (writer != null)
                {
                    writer.Close();
                }
            }

        } //main

        bool findColumnInTables(TColumn column, TCustomSqlStatement select, int level, string columnName)
        {
            bool ret = false;
            foreach (String tableName in column.tableNames)
            {
                if (columnName != null && filter != null)
                {
                    int dotIndex = tableName.LastIndexOf(".");
                    string tableOwner = null;
                    string tableRealName = null;
                    if (dotIndex >= 0)
                    {
                        tableOwner = tableName.Substring(0, dotIndex);
                        tableRealName = tableName.Replace(tableOwner + ".", "");
                    }
                    else
                    {
                        tableRealName = tableName;
                    }
                    if (filter.metaDatabaseTableColumn(tableOwner, tableRealName, columnName))
                    {
                        column.columnName = columnName;
                        //column.orignColumn = "*";
                        ret |= findColumnInTables(column, tableName, select, level);
                    }
                    else ret |= false;
                }
                else ret |= findColumnInTables(column, tableName, select, level);
            }
            return ret;
        }

        bool findColumnInTables(TColumn column, String tableName, TCustomSqlStatement select, int level)
        {
            return findColumnInTables(column, tableName, select, level, ClauseType.undefine);
        }

        bool findColumnInTables(TColumn column, String tableName, TCustomSqlStatement select, int level, ClauseType clause)
        {
            bool ret = false;
            TLzTableList tables = select.Tables;

            if (tables.Count() == 1)
            {
                TLzTable lzTable = tables[0];
                //buffer.AppendLine(lzTable.AsText);
                if ((lzTable.TableType == TLzTableType.lttAttr)
                    && (lzTable.TableAlias.Length == 0))
                {
                    ret = true;

                    if (!simply)
                    {
                        buffer.AppendLine(new string(' ', level) + "--> " + lzTable.AsText + "." + column.columnName);
                    }
                    if (cteMap.ContainsKey(lzTable.Name))
                    {
                        if (!simply)
                        {
                            buffer.AppendLine(new string(' ', level) + "--> WITH CTE");
                        }
                        ret = findColumnInSubQuery((TSelectSqlStatement)cteMap[lzTable.Name], column.columnName, level + 1);
                    }
                    else
                    {
                        if (currentSource != null && dependMap.ContainsKey(currentSource) && dependMap[currentSource] is List<TResultEntry>)
                        {
                            TCustomSqlStatement stmt = containClasuse(currentClauseMap, select);
                            if (stmt != null)
                            {
                                ((List<TResultEntry>)dependMap[currentSource]).Add(new TResultEntry(lzTable, column.columnName, (ClauseType)currentClauseMap[stmt]));
                            }
                            else if (select is TSelectSqlStatement)
                            {
                                if (ClauseType.undefine.Equals(clause))
                                    ((List<TResultEntry>)dependMap[currentSource]).Add(new TResultEntry(lzTable, column.columnName, ClauseType.select));
                                else
                                    ((List<TResultEntry>)dependMap[currentSource]).Add(new TResultEntry(lzTable, column.columnName, clause));
                            }
                            else
                            {
                                ((List<TResultEntry>)dependMap[currentSource]).Add(new TResultEntry(lzTable, column.columnName, ClauseType.undefine));
                            }
                        }
                    }
                }
            }

            if (ret) return ret;

            foreach (TLzTable lztable in tables)
            {
                switch (lztable.TableType)
                {
                    case TLzTableType.lttAttr:
                        TTable table = TLzTaleToTable(lztable);
                        String alias = table.tableAlias;
                        if (alias != null) alias = alias.Trim();
                        if ((tableName != null) &&
                            ((tableName.Equals(alias, StringComparison.OrdinalIgnoreCase)) || (tableName.Equals(table.tableName, StringComparison.OrdinalIgnoreCase)))
                           )
                        {
                            if (!simply)
                            {
                                buffer.AppendLine(new string(' ', level) + "--> " + table.tableName + "." + column.columnName);
                            }
                            if (cteMap.ContainsKey(lztable.Name))
                            {
                                if (!simply)
                                {
                                    buffer.AppendLine(new string(' ', level) + "--> WITH CTE");
                                }
                                ret = findColumnInSubQuery((TSelectSqlStatement)cteMap[lztable.Name], column.columnName, level + 1);
                            }
                            else
                            {
                                if (dependMap.ContainsKey(currentSource) && dependMap[currentSource] is List<TResultEntry>)
                                {
                                    if (currentClauseMap.ContainsKey(select))
                                    {
                                        ((List<TResultEntry>)dependMap[currentSource]).Add(new TResultEntry(lztable, column.orignColumn, (ClauseType)currentClauseMap[select]));
                                    }
                                    else if (select is TSelectSqlStatement)
                                    {
                                        ((List<TResultEntry>)dependMap[currentSource]).Add(new TResultEntry(lztable, column.orignColumn, ClauseType.select));
                                    }
                                    else
                                    {
                                        ((List<TResultEntry>)dependMap[currentSource]).Add(new TResultEntry(lztable, column.orignColumn, ClauseType.undefine));
                                    }
                                }
                                ret = true;
                            }
                        }
                        break;
                    case TLzTableType.lttSubquery:
                        for (int i = 0; i < column.tableNames.Count; i++)
                        {
                            String name = column.tableNames[i];
                            TSelectSqlStatement selectStat = (TSelectSqlStatement)lztable.SubQuery;

                            if (name == null)
                            {
                                ret = findColumnInSubQuery(selectStat, column.columnName, level + 1);
                                break;
                            }

                            if (lztable.AliasClause != null && lztable.AliasClause.AsText.Trim().Equals(name, StringComparison.OrdinalIgnoreCase))
                            {
                                ret = findColumnInSubQuery(selectStat, column.columnName, level + 1);
                                break;
                            }

                            bool flag = false;
                            for (int j = 0; j < selectStat.Tables.Count(); j++)
                            {
                                if (selectStat.Tables[i].AliasClause != null)
                                {
                                    if (selectStat.Tables[i].TableAlias.Equals(name, StringComparison.OrdinalIgnoreCase))
                                    {
                                        ret = findColumnInSubQuery(selectStat, column.columnName, level + 1);
                                        flag = true;
                                        break;
                                    }
                                }
                                else
                                {
                                    if (selectStat.Tables[i].TableName.Equals(name, StringComparison.OrdinalIgnoreCase))
                                    {
                                        ret = findColumnInSubQuery(selectStat, column.columnName, level + 1);
                                        flag = true;
                                        break;
                                    }
                                }
                            }
                            if (flag) break;
                        }
                        break;
                    default:
                        break;
                }
                if (ret) break;
            }

            return ret;
        }

        private TCustomSqlStatement containClasuse(Hashtable currentClauseMap, TCustomSqlStatement select)
        {
            if (currentClauseMap.Contains(select))
                return select;
            else if (select.OwnerStmt is TCustomSqlStatement)
            {
                return containClasuse(currentClauseMap, (TCustomSqlStatement)select.OwnerStmt);
            }
            else return null;
        }

        public bool findColumnInSubQuery(TSelectSqlStatement select, string columnName, int level)
        {
            bool ret = false;
            if (select.SelectSetType != TSelectSetType.sltNone)
            {
                bool left = findColumnInSubQuery(select.LeftStmt, columnName, level + 1);
                bool right = findColumnInSubQuery(select.RightStmt, columnName, level + 1);
                ret = left && right;
            }
            else
            {
                // check colum name in select list of subquery
                TLzField columnField = null;
                if (!"*".Equals(columnName))
                {
                    foreach (TLzField field in select.Fields)
                    {
                        if (field.FieldAlias.Length > 0)
                        {
                            if (field.FieldAlias.Equals(columnName, StringComparison.OrdinalIgnoreCase))
                            {
                                columnField = field;
                                break;
                            }
                        }
                        else
                        {
                            if (field.FieldType == TLzFieldType.lftAttr)
                            {
                                TColumn column = attrToColumn(field.FieldAttr, select);
                                if (columnName.Equals(column.columnName, StringComparison.OrdinalIgnoreCase))
                                {
                                    columnField = field;
                                    break;
                                }
                            }
                        }
                    }
                }
                foreach (TLzField field in select.Fields)
                {
                    if (columnField != null && !field.Equals(columnField))
                    {
                        continue;
                    }
                    if (field.FieldAlias.Length > 0)
                    {
                        ret = "*".Equals(columnName) || field.FieldAlias.Equals(columnName, StringComparison.OrdinalIgnoreCase);
                        if (ret)
                        {
                            // let's check where this column come from?
                            if (!simply)
                            {
                                buffer.AppendLine(new string(' ', level) + "--> " + field.FieldAlias + "(alias)");
                            }
                            linkFieldToTables(field, select, level + 1);
                        }
                    }
                    else
                    {
                        if (field.FieldType == TLzFieldType.lftAttr)
                        {
                            TColumn column = attrToColumn(field.FieldAttr, select);
                            ret = "*".Equals(columnName) || columnName.Equals(column.columnName, StringComparison.OrdinalIgnoreCase);
                            if (ret || "*".Equals(column.columnName))
                            {
                                findColumnInTables(column, select, level, ret == false ? columnName : null);
                                findColumnsFromClauses(select, level + 1);
                            }
                        }
                    }

                    if (ret && !"*".Equals(columnName)) break;
                }
            }
            return ret;
        } // findColumnInSubQuery

        public bool linkFieldToTables(TLzField field, TCustomSqlStatement select, int level)
        {
            bool ret = false;
            //TLzTableList tables = select.Tables;
            // all items in select list was represented by a TLzField Objects
            switch (field.FieldType)
            {
                case TLzFieldType.lftAttr:
                    TColumn column = attrToColumn(field.FieldAttr, select);
                    if (level == 0)
                    {
                        TAlias alias = new TAlias();
                        alias.column = field.AsText;
                        alias.alias = field.AsText;
                        if (field.aliasclause != null)
                        {
                            alias.alias = field.FieldAlias;
                            alias.column = field.FieldFullname;
                        }
                        aliases.Add(alias);
                        currentSource = alias.alias;
                        if (!dependMap.ContainsKey(currentSource))
                            dependMap.Add(currentSource, new List<TResultEntry>());

                        if (!simply)
                        {
                            if (!alias.alias.Equals(column.getOrigName()))
                            {
                                buffer.AppendLine(System.Environment.NewLine + "Search " + alias.alias);
                                buffer.AppendLine("--> " + column.getOrigName());
                            }
                            buffer.AppendLine(System.Environment.NewLine + "Search " + column.getOrigName());
                        }

                    }
                    if (select.DBVendor == TDbVendor.DbVOracle && this.isPseudocolumn(column.columnName))
                    {
                        break;
                    }
                    ret = findColumnInTables(column, select, level, null);
                    findColumnsFromClauses(select, level + 1);
                    break;
                case TLzFieldType.lftExpression:
                    if (level == 0)
                    {
                        TAlias alias = new TAlias();

                        if (select is TUpdateSqlStatement)
                        {
                            alias.column = field.FieldExpr.lexpr.AsText;
                            alias.alias = alias.column;
                        }
                        else
                        {
                            alias.column = field.AsText;
                            alias.alias = alias.column;

                        }
                        if (field.aliasclause != null)
                        {
                            alias.alias = field.FieldAlias;
                            alias.column = field.FieldFullname;
                        }
                        aliases.Add(alias);
                        if (!simply)
                        {
                            buffer.AppendLine(System.Environment.NewLine + "Search " + alias.alias);
                        }

                        currentSource = alias.alias;
                        if (!dependMap.ContainsKey(currentSource))
                            dependMap.Add(currentSource, new List<TResultEntry>());
                    }

                    List<TColumn> columns = exprToColumn(field.FieldExpr, select, level);
                    if (select is TUpdateSqlStatement)
                    {
                        while (columns.Count > 1)
                        {
                            columns.RemoveAt(columns.Count - 1);
                        }
                    }
                    if (!simply)
                    {
                        foreach (TColumn column1 in columns)
                        {
                            if (level == 0)
                            {
                                buffer.AppendLine(new string(' ', level) + "--> " + column1.getOrigName());
                            }
                        }
                    }

                    foreach (TColumn column1 in columns)
                    {
                        if (level == 0)
                        {
                            if (!simply)
                            {
                                buffer.AppendLine(System.Environment.NewLine + "Search " + column1.getOrigName());
                            }
                        }
                        if (!(select is TUpdateSqlStatement))
                        {
                            findColumnInTables(column1, select, level, null);
                        }
                        findColumnsFromClauses(select, level + 1);
                    }


                    if (field.FieldExpr.oper == TLzOpType.Expr_FuncCall)
                    {
                        TLz_FuncCall func = (TLz_FuncCall)field.FieldExpr.lexpr;
                        // buffer.AppendLine("function name {0}", func.funcname.AsText);
                        if ((func.funcname.AsText.Equals("count", StringComparison.OrdinalIgnoreCase))
                            || (func.funcname.AsText.Equals("sum", StringComparison.OrdinalIgnoreCase))
                            || (func.funcname.AsText.Equals("row_number", StringComparison.OrdinalIgnoreCase))
                            )
                        {
                            if (!simply)
                            {
                                buffer.AppendLine(new string(' ', level) + "--> aggregate function " + func.AsText);
                                for (int i = 0; i < select.Tables.Count(); i++)
                                {
                                    buffer.AppendLine(new string(' ', level) + "--> table " + select.Tables[i].AsText);
                                }
                            }
                            // check column in function arguments
                            if (func.args != null)
                            {
                                for (int k = 0; k < func.args.Count(); k++)
                                {
                                    if (func.args[k] is TLzCustomExpression)
                                    {
                                        TLzCustomExpression expr = (TLzCustomExpression)func.args[k];
                                        List<TColumn> columns1 = exprToColumn(expr, select, level);
                                        foreach (TColumn column1 in columns1)
                                        {
                                            findColumnInTables(column1, select, level, null);
                                            findColumnsFromClauses(select, level + 1);
                                        }
                                    }
                                }
                            }
                            else
                            {
                                if (dependMap.ContainsKey(currentSource) && dependMap[currentSource] is List<TResultEntry>)
                                {

                                    if (currentClauseMap.ContainsKey(select))
                                    {
                                        ((List<TResultEntry>)dependMap[currentSource]).Add(new TResultEntry(select.Tables[0], "*", (ClauseType)currentClauseMap[select]));
                                    }
                                    else if (select is TSelectSqlStatement)
                                    {
                                        ((List<TResultEntry>)dependMap[currentSource]).Add(new TResultEntry(select.Tables[0], "*", ClauseType.select));
                                    }
                                    else
                                    {
                                        ((List<TResultEntry>)dependMap[currentSource]).Add(new TResultEntry(select.Tables[0], "*", ClauseType.undefine));
                                    }
                                }
                            }

                            if (func._analyticFunction != null)
                            {
                                TLz_List list = func._analyticFunction._partitionExprList;

                                findColumnsFromList(select, level, list);


                                if (func._analyticFunction._sortclause != null)
                                {
                                    list = func._analyticFunction._sortclause.list1;

                                    findColumnsFromList(select, level, list);


                                    list = func._analyticFunction._sortclause.list2;

                                    findColumnsFromList(select, level, list);

                                }
                            }


                            findColumnsFromClauses(select, level);


                        }
                    }
                    break;
                case TLzFieldType.lftSubquery:
                    TAlias alias1 = new TAlias();
                    alias1.column = field.AsText;
                    alias1.alias = field.AsText;
                    if (field.aliasclause != null)
                    {
                        alias1.alias = field.FieldAlias;
                        alias1.column = field.FieldFullname;
                    }
                    if (level == 0)
                    {
                        aliases.Add(alias1);
                        if (!simply)
                        {
                            buffer.AppendLine(System.Environment.NewLine + "Search " + alias1.alias);
                        }
                    }
                    findColumnInSubQuery((TSelectSqlStatement)field.SubQuery, alias1.alias, level);
                    break;
                default:
                    buffer.AppendLine("linkFieldToTables of type: " + field.FieldType + " not implemented yet");
                    break;
            }

            return ret;
        }

        private void findColumnsFromGroupBy(TCustomSqlStatement select, int level)
        {
            if (select is TSelectSqlStatement && ((TSelectSqlStatement)select).GroupbyClause != null)
            {
                for (int j = 0; j < ((TSelectSqlStatement)select).GroupbyClause.GroupItems.Count(); j++)
                {
                    TLzGroupByItem i = (TLzGroupByItem)((TSelectSqlStatement)select).GroupbyClause.GroupItems[j];
                    List<TColumn> columns1 = exprToColumn((TLzCustomExpression)i._ndExpr, select, level);

                    TColumn column1 = columns1[0];
                    foreach (String tableName in column1.tableNames)
                    {
                        if (!simply)
                        {
                            buffer.AppendLine(new string(' ', level) + "--> " + column1.getFullName(tableName) + "(group by)");
                        }
                        findColumnInTables(column1, tableName, select, level, ClauseType.groupby);
                    }

                }

            }
        }

        private bool isPseudocolumn(string column)
        {
            if (column == null)
                return false;
            if ("rownum".Equals(column.Trim().ToLower()))
                return true;
            else if ("rowid".Equals(column.Trim().ToLower()))
                return true;
            else if ("nextval".Equals(column.Trim().ToLower()))
                return true;
            else if ("sysdate".Equals(column.Trim().ToLower()))
                return true;
            return false;
        }

        private void findColumnsFromClauses(TCustomSqlStatement select, int level)
        {
            currentClauseMap.Add(select, ClauseType.undefine);
            Hashtable clauseTable = new Hashtable();
            if (select is TSelectSqlStatement)
            {

                TSelectSqlStatement statement = (TSelectSqlStatement)select;


                if (statement.SortClause != null)
                {
                    TLzOrderByList sortList = (TLzOrderByList)statement.SortClause;
                    for (int i = 0; i < sortList.Count(); i++)
                    {
                        TLzOrderBy orderBy = sortList[i];
                        TLzCustomExpression expr = orderBy.SortExpr;
                        clauseTable.Add(expr, ClauseType.orderby);
                    }
                }


                if (statement.WhereClause != null)
                {
                    clauseTable.Add((TLzCustomExpression)(statement.WhereClause), ClauseType.where);
                }
                if (statement.ConnectByClause != null)
                {
                    clauseTable.Add((TLzCustomExpression)statement.ConnectByClause, ClauseType.connectby);
                }
                if (statement.StartwithClause != null)
                {
                    clauseTable.Add((TLzCustomExpression)statement.StartwithClause, ClauseType.startwith);
                }
                if (statement.JoinTables != null)
                {
                    for (int i = 0; i < statement.JoinTables.Count(); i++)
                    {
                        TLzJoin join = statement.JoinTables[i];
                        if (join.JoinItems != null)
                        {
                            for (int j = 0; j < join.JoinItems.Count(); j++)
                            {
                                TLzJoinItem joinItem = join.JoinItems[j];
                                TLzCustomExpression expr = joinItem.JoinQual;
                                if (expr != null)
                                    clauseTable.Add(expr, ClauseType.join);
                            }
                        }
                    }
                }
            }
            else if (select is TUpdateSqlStatement)
            {
                TUpdateSqlStatement statement = (TUpdateSqlStatement)select;
                if (statement.SortClause != null)
                {
                    TLzOrderByList sortList = (TLzOrderByList)statement.SortClause;
                    for (int i = 0; i < sortList.Count(); i++)
                    {
                        TLzOrderBy orderBy = sortList[i];
                        TLzCustomExpression expr = orderBy.SortExpr;
                        clauseTable.Add(expr, ClauseType.orderby);
                    }
                }
                if (statement.WhereClause != null)
                {
                    clauseTable.Add((TLzCustomExpression)(statement.WhereClause), ClauseType.where);
                }

                if (statement.JoinTables != null)
                {
                    for (int i = 0; i < statement.JoinTables.Count(); i++)
                    {
                        TLzJoin join = statement.JoinTables[i];
                        if (join.JoinItems != null)
                        {
                            for (int j = 0; j < join.JoinItems.Count(); j++)
                            {
                                TLzJoinItem joinItem = join.JoinItems[j];
                                TLzCustomExpression expr = joinItem.JoinQual;
                                if (expr != null)
                                    clauseTable.Add(expr, ClauseType.join);
                            }
                        }
                    }
                }
            }

            foreach (TLzCustomExpression expr in clauseTable.Keys)
            {
                currentClauseMap[select] = (ClauseType)clauseTable[expr];

                if (!simply)
                {
                    switch ((ClauseType)currentClauseMap[select])
                    {
                        case ClauseType.where:
                            buffer.AppendLine(new string(' ', level) + "--> Where Clause");
                            break;
                        case ClauseType.connectby:
                            buffer.AppendLine(new string(' ', level) + "--> Connect By Clause");
                            break;
                        case ClauseType.startwith:
                            buffer.AppendLine(new string(' ', level) + "--> Start With Clause");
                            break;
                        case ClauseType.orderby:
                            buffer.AppendLine(new string(' ', level) + "--> Order By Clause");
                            break;
                        case ClauseType.join:
                            buffer.AppendLine(new string(' ', level) + "--> Join");
                            break;
                    }

                }

                List<TColumn> columns = exprToColumn(expr, select, level);
                foreach (TColumn column1 in columns)
                {
                    foreach (String tableName in column1.tableNames)
                    {
                        if (!simply)
                        {

                            switch ((ClauseType)currentClauseMap[select])
                            {
                                case ClauseType.where:
                                    buffer.AppendLine(new string(' ', level + 1) + "--> " + column1.getFullName(tableName) + "(Where)");
                                    break;
                                case ClauseType.connectby:
                                    buffer.AppendLine(new string(' ', level + 1) + "--> " + column1.getFullName(tableName) + "(Connect By)");
                                    break;
                                case ClauseType.startwith:
                                    buffer.AppendLine(new string(' ', level + 1) + "--> " + column1.getFullName(tableName) + "(Start With)");
                                    break;
                                case ClauseType.orderby:
                                    buffer.AppendLine(new string(' ', level + 1) + "--> " + column1.getFullName(tableName) + "(Order By)");
                                    break;
                                case ClauseType.join:
                                    buffer.AppendLine(new string(' ', level + 1) + "--> " + column1.getFullName(tableName) + "(Join)");
                                    break;
                            }

                        }
                        findColumnInTables(column1, tableName, select, level);
                    }

                }
            }
            currentClauseMap.Remove(select);

            // check order by clause
            findColumnsFromGroupBy(select, level);
        }

        private void findColumnsFromList(TCustomSqlStatement select, int level, TLz_List list)
        {
            if (list == null)
                return;

            for (int i = 0; i < list.Count(); i++)
            {
                TLzCustomExpression lcexpr = null;
                if (list[i] is TLz_SortGroupBy)
                {
                    lcexpr = (TLzCustomExpression)((TLz_SortGroupBy)list[i]).node;
                }
                else if (list[i] is TLzCustomExpression)
                {
                    lcexpr = (TLzCustomExpression)list[i];
                }

                if (lcexpr != null)
                {
                    List<TColumn> columns = exprToColumn(lcexpr, select, level);
                    foreach (TColumn column1 in columns)
                    {
                        findColumnInTables(column1, select, level, null);
                        findColumnsFromClauses(select, level);
                    }
                }
            }
        }

        public TColumn attrToColumn(TLz_Attr attr, TCustomSqlStatement stmt)
        {
            TColumn column = new TColumn();
            column.columnName = attr.LastToken.AsText;
            if (attr.AsText.IndexOf(".") > 0)
            {
                column.columnPrex = attr.AsText.Substring(0, attr.AsText.Length - column.columnName.Length - ".".Length);
                String tableName = column.columnPrex;
                if (!column.tableNames.Contains(tableName))
                {
                    column.tableNames.Add(tableName);
                }
            }
            else
            {
                TLzTableList tables = stmt.Tables;
                foreach (TLzTable lztable in tables)
                {
                    TTable table = TLzTaleToTable(lztable);
                    if (!column.tableNames.Contains(table.tableName))
                    {
                        column.tableNames.Add(table.tableName);
                    }
                }
            }

            column.orignColumn = column.columnName;

            return column;
        }

        TTable TLzTaleToTable(TLzTable lztable)
        {
            TTable table = new TTable();
            if (lztable.TableAttr != null && lztable.TableAttr.LastToken != null)
            {
                table.tableName = lztable.TableAttr.LastToken.AsText;
                if (lztable.TableAttr.AsText.IndexOf(".") > 0)
                {
                    table.prefixName = lztable.TableAttr.AsText.Substring(0, lztable.TableAttr.AsText.Length - table.tableName.Length - ".".Length);
                }
            }

            if (lztable.AliasClause != null)
            {
                table.tableAlias = lztable.AliasClause.AsText;
            }

            return table;

        }


        List<TColumn> exprToColumn(TLzCustomExpression expr, TCustomSqlStatement stmt, int level)
        {
            List<TColumn> columns = new List<TColumn>();

            columnsInExpr c = new columnsInExpr(this, expr, columns, stmt, level);
            c.searchColumn();

            return columns;
        }


    }//class columnImpact
}
